package com.ling.simul.utils;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.ling.simul.config.*;
import com.ling.simul.constant.CommonConstant;
//import com.ling.simul.constant.QueryRecord;
import lombok.extern.apachecommons.CommonsLog;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.util.StringUtils;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.net.InetAddress;
import java.net.UnknownHostException;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.prefs.BackingStoreException;


@CommonsLog
public class CommonUtil {

    public static List startPage(List list, Integer pageNum, Integer pageSize) {
        if (list == null) {
            return new ArrayList();
        }
        if (list.size() == 0) {
            return new ArrayList();
        }
        // 记录总数
        Integer count = list.size();
        // 页数
        int pageCount = 0;
        if (count % pageSize == 0) {
            pageCount = count / pageSize;
        } else {
            pageCount = count / pageSize + 1;
        }
        // 开始索引
        int fromIndex = 0;
        // 结束索引
        int toIndex = 0;
        if (!pageNum.equals(pageCount)) {
            fromIndex = (pageNum - 1) * pageSize;
            toIndex = fromIndex + pageSize;
        } else {
            fromIndex = (pageNum - 1) * pageSize;
            toIndex = count;
        }
        return list.subList(fromIndex, toIndex);
    }

    // 组装dataModel
    public static JSONObject setDataModel(String theme, ThemeRelationConfig themeConfig){
        JSONObject kvs = new JSONObject();
        if (themeConfig.getKeys() != null){
            List<String> kvList = themeConfig.getKeys();
            JSONArray kvArray= JSONArray.parseArray(JSON.toJSONString(kvList));
            kvs.put("kv", kvArray);
        }

        // 添加各道次中某个指标的取数方法
        if (themeConfig.getPass() != null){
            String name = themeConfig.getPass().getName();
            List<String> passList = themeConfig.getPass().getValues();
            JSONArray passArray= JSONArray.parseArray(JSON.toJSONString(passList));
            kvs.put(name, passArray);
        }
        // 添加各道次中某个指标的取数方法
        if (themeConfig.getPass1() != null){
            String name = themeConfig.getPass1().getName();
            List<String> passList = themeConfig.getPass1().getValues();
            JSONArray passArray= JSONArray.parseArray(JSON.toJSONString(passList));
            kvs.put(name, passArray);
        }
        // 添加各道次中某个指标的取数方法
        if (themeConfig.getPass2() != null){
            String name = themeConfig.getPass2().getName();
            List<String> passList = themeConfig.getPass2().getValues();
            JSONArray passArray= JSONArray.parseArray(JSON.toJSONString(passList));
            kvs.put(name, passArray);
        }

        JSONObject dataModel = new JSONObject();
        dataModel.put("theme", theme);
        dataModel.put("kvs", kvs);
        return dataModel;
    }

    // 组装filter
    public static JSONArray setFilter(JSONArray filterArray, String name, String type, Object value, String theme){
        if (StringUtils.isEmpty(value) || (value instanceof JSONArray && ((JSONArray) value).isEmpty())){
            return filterArray;
        }
        JSONObject op = new JSONObject();
        op.put(type, value);

        JSONObject filter = new JSONObject();
        filter.put("name", name);
        filter.put("op", op);
        filter.put("theme", theme);
        filterArray.add(filter);
        return filterArray;
    }

    // aggregate.count 参数封装
    public static JSONObject setCountParam(Relation relation, Map<String, Object> contentMap){
        JSONArray filterData = new JSONArray();
        for(Filter filter : relation.getFilterList()){
            CommonUtil.setFilter(filterData, filter.getKey(), filter.getOption(), contentMap.get(filter.getCondition()), filter.getTheme());
        }
        JSONArray aggregate = new JSONArray();
        JSONObject group = new JSONObject();
        group.put("count", "num");
        if(CommonConstant.DATA_CENTER_LINE_CAL.equals(relation.getLine())){
            group.put("theme", "CALPDI");
        }else {
            group.put("theme", "PDI");
        }
        group.put("name", "null");
        JSONObject temp = new JSONObject();
        temp.put("$group", group);
        aggregate.add(temp);

        JSONObject bodyFirst = new JSONObject();
        bodyFirst.put("aggregate", aggregate);
        bodyFirst.put("filter", filterData);
        bodyFirst.put("line", relation.getLine());
        bodyFirst.put("record", CommonConstant.DATA_CENTER_RECORD);

        JSONArray body = new JSONArray();
        body.add(bodyFirst);
        JSONObject params = new JSONObject();
        params.put("action", CommonConstant.DATA_CENTER_ACTION);
        params.put("operator", CommonConstant.DATA_CENTER_OPERATOR);
        params.put("terminal", CommonConstant.DATA_CENTER_TERMINAL);
        params.put("body", body);
        return params;
    }
    // aggregate.count 参数封装 增加对null过滤的检索条件
    public static JSONObject setNotNullCountParam(Relation relation, Map<String, Object> contentMap,List<String>list){
        JSONArray filterData = new JSONArray();
        for(Filter filter : relation.getFilterList()){
            CommonUtil.setFilter(filterData, filter.getKey(), filter.getOption(), contentMap.get(filter.getCondition()), filter.getTheme());
        }

        String ss = "{\"$ne\":null}";
        JSONObject json = JSONObject.parseObject(ss);
        for (String s:list){
//            CommonUtil.setFilter(filterData, s.split(";")[0], "$ne", "\0null", s.split(";")[1]);
//            JSONObject filter = new JSONObject();
//            filter.put("name", s.split(";")[0]);
//            filter.put("op", json);
//            filter.put("theme", s.split(";")[1]);
//            filterData.add(filter);
            CommonUtil.setFilter(filterData, s.split(";")[0], "$ne", "#$*%#", s.split(";")[1]);
        }
        JSONArray aggregate = new JSONArray();
        JSONObject group = new JSONObject();
        group.put("count", "num");
        if(CommonConstant.DATA_CENTER_LINE_CAL.equals(relation.getLine())){
            group.put("theme", "CALPDI");
        }else {
            group.put("theme", "PDI");
        }
        group.put("name", "null");
        JSONObject temp = new JSONObject();
        temp.put("$group", group);
        aggregate.add(temp);

        JSONObject bodyFirst = new JSONObject();
        bodyFirst.put("aggregate", aggregate);
        bodyFirst.put("filter", filterData);
        bodyFirst.put("line", relation.getLine());
        bodyFirst.put("record", CommonConstant.DATA_CENTER_RECORD);

        JSONArray body = new JSONArray();
        body.add(bodyFirst);
        JSONObject params = new JSONObject();
        params.put("action", CommonConstant.DATA_CENTER_ACTION);
        params.put("operator", CommonConstant.DATA_CENTER_OPERATOR);
        params.put("terminal", CommonConstant.DATA_CENTER_TERMINAL);
        params.put("body", body);
        return params;
    }

    // 聚合函数配置
    private static JSONObject setAggrParamByRelation(Relation relation, Map<String, Object> contentMap) {
        // 装配aggregate
        JSONArray aggregateJson = new JSONArray();
        List<Aggregate> aggregateList = relation.getAggregateList();
        for(Aggregate aggregate : aggregateList){
            JSONObject optionData = new JSONObject();
            String option = aggregate.getOption();
            List<ThemeRelationConfig> themeRelationConfigList = aggregate.getThemeRelation();
            JSONObject configData = new JSONObject();
            for(ThemeRelationConfig config : themeRelationConfigList){
                configData.put(config.getTheme(), config.getKeys());
            }
            optionData.put(option, configData);
            aggregateJson.add(optionData);
        }

        // 聚合函数可能不存在group 这里处理一下
        Group group = relation.getGroup();
        if (group != null){
            JSONObject groupTemp = new JSONObject();
            groupTemp.put("count", group.getCount());
            groupTemp.put("theme", group.getTheme());
            groupTemp.put("name", group.getName());
            JSONObject groupData = new JSONObject();
            groupData.put("$group", groupTemp);
            aggregateJson.add(groupData);
        }

        // 装配filter
        JSONArray filterData = new JSONArray();
        for(Filter filter : relation.getFilterList()){
            CommonUtil.setFilter(filterData, filter.getKey(), filter.getOption(), contentMap.get(filter.getCondition()), filter.getTheme());
        }
        JSONArray body = new JSONArray();
        JSONObject bodyFirst = new JSONObject();
        bodyFirst.put("aggregate", aggregateJson);
        bodyFirst.put("filter", filterData);
        bodyFirst.put("line", relation.getLine());
        bodyFirst.put("record", CommonConstant.DATA_CENTER_RECORD);
        body.add(bodyFirst);

        JSONObject params = new JSONObject();
        params.put("action", CommonConstant.DATA_CENTER_ACTION);
        params.put("operator", CommonConstant.DATA_CENTER_OPERATOR);
        params.put("terminal", CommonConstant.DATA_CENTER_TERMINAL);
        params.put("body", body);
        return params;
    }

    // 根据配置的Relation 装配数据接口的入参
    public static JSONObject setParamByRelation(Relation relation, Map<String, Object> contentMap){
        JSONObject bodyFirst = new JSONObject();
        List<Aggregate> aggregateList = relation.getAggregateList();
        if (aggregateList != null){
            return setAggrParamByRelation(relation, contentMap);
        }
        // 装配data
        JSONArray data = new JSONArray();
        for(ThemeRelationConfig themeConfig : relation.getThemeRelation()){
            data.add(CommonUtil.setDataModel(themeConfig.getTheme(), themeConfig));
        }
        // 装配filter
        JSONArray filterData = new JSONArray();
        for(Filter filter : relation.getFilterList()){
            CommonUtil.setFilter(filterData, filter.getKey(), filter.getOption(), contentMap.get(filter.getCondition()), filter.getTheme());
        }
        // 装配page
        Page page = relation.getPage();
        // 这里增加一条修改 因为存在查询单条 不需要分页的情况 跟徐博士沟通的结果是 page字段不传 就不会分页
        if (page != null){
            JSONObject pageData = CommonUtil.setPage((Integer) contentMap.get(page.getSkipCondition()), (Integer)contentMap.get(page.getLimitCondition()));
            bodyFirst.put("page", pageData);
        }

        // 装配sort
        Sort sort = relation.getSort();
        if (sort != null){
            JSONArray sortData = new JSONArray();
            sortData.add(CommonUtil.setSort(sort.getName(), sort.getAsc(), sort.getTheme()));
            bodyFirst.put("sort", sortData);
        }

        JSONArray body = new JSONArray();
        bodyFirst.put("data", data);
//        if(!filterData.isEmpty()){
        bodyFirst.put("filter", filterData);
//        }
        bodyFirst.put("line", relation.getLine());
        bodyFirst.put("record", CommonConstant.DATA_CENTER_RECORD);
        body.add(bodyFirst);

        JSONObject params = new JSONObject();
        params.put("action", CommonConstant.DATA_CENTER_ACTION);
        params.put("operator", CommonConstant.DATA_CENTER_OPERATOR);
        params.put("terminal", CommonConstant.DATA_CENTER_TERMINAL);
        params.put("body", body);
        return params;
    }

    // pass下的数据按照索引获取
    public static Object getFromDataPass(List<Object> rmPassData, Integer num1, Integer num2, Integer r1PassTotal, Integer r2PassTotal) {
        if(rmPassData==null || rmPassData.isEmpty()){
            return null;
        }
        // rm 第一条数据 rm_pass_no为1.0的是ssp的
        Integer index = 0;
        if (num1 == 1){
            index = num2;
        }else if(num1 == 2){
            if (num2 > r2PassTotal){
                return null;
            }
            index = r1PassTotal + num2;
        }
//        if(index >= rmPassData.size()){
//            return rmPassData.get(rmPassData.size()-1);
//        }
        return rmPassData.get(index);
    }
    // pass下的数据按照索引获取 由于压下率和压下量没有ssp，所以需要单独处理
    public static Object getFromDataPass1(List<Object> rmPassData, Integer num1, Integer num2, Integer r1PassTotal, Integer r2PassTotal) {
        if(rmPassData==null || rmPassData.isEmpty()){
            return null;
        }
        // rm 第一条数据 rm_pass_no为1.0的是ssp的
        Integer index = 0;
        if (num1 == 1){
            index = num2-1;
        }else if(num1 == 2){
            if (num2 > r2PassTotal){
                return null;
            }
            index = r1PassTotal + num2 -1;
        }
//        if(index >= rmPassData.size()){
//            return rmPassData.get(rmPassData.size()-1);
//        }
        return rmPassData.get(index);
    }
    // pass下的数据按照索引获取
    public static JSONArray getIndexJson(int size){
        JSONArray result = new JSONArray();
        if (size > 0){
            for(int i= 1; i <= size; i++){
                result.add(i);
            }
        }
        return result;
    }

    // 按照挡位数量给数组分层
    public static List<JSONObject> getGear(List<Float> dataRange){
        int gear = 10;// 默认为10
        List<JSONObject> result = new ArrayList<>();
        Float min = Collections.min(dataRange);
        Float max = Collections.max(dataRange);
        Float diff = (max - min)/gear;
        for(int i= 1; i <= gear; i++){
            JSONObject json = new JSONObject();
            json.put("min", min + (i-1)*diff);
            json.put("max", min + i*diff);
            result.add(json);
        }
        return result;
    }

    // 排除空值 并根据数据源优先级提供数据 result1优先
    public static Object filterNull(Object result1, Object result2) {
        if(result1 != null){
            return result1;
        }else{
            return result2;
        }
    }
    public static JSONArray getLenthPosition(Object value,int a) {
        JSONArray lengthArray = new JSONArray();
        double length = trans2Double(value, 400d);
        double countNum = (length)/a;
        for(int i=1;i<=a;i++){
            lengthArray.add(i*countNum);
        }
        return lengthArray;
    }
    public static JSONArray getLenthPosition(Object value) {
        JSONArray lengthArray = new JSONArray();
        int length = (int) Math.floor(trans2Double(value, 400d));
        // 长度点位规则 前段15个从1开始以1递增 中段从19开始以5递增 后端最后15个以1递增到最后
        int countNum = (length - 30)/5 -1;
        for(int i =1; i<= 15; i++){
            lengthArray.add(i);
        }

        for(int i=19; i<= 19+countNum*5; i=i+5){
            lengthArray.add(i);
        }

        for(int i=length-13; i <= length+1; i++){
            lengthArray.add(i);
        }
        return lengthArray;
    }

    public static JSONArray getWidthPosition(Object value) {
        JSONArray widthArray = new JSONArray();
        Double length = trans2Double(value, 1200d);
        Double count = length/40;
        for(int i=0; i< 41; i++){
            widthArray.add(count * i);
        }
        return widthArray;
    }

    public static Double trans2Double(Object value, Double defaultValue){
        Double b;
        if (value instanceof Integer){
            b = Double.valueOf(((Integer) value).intValue());
        }else if (value == null || value.equals("")){
            b = new Double(defaultValue);
        }else{
            b = ((BigDecimal)value).doubleValue();
        }
        return b;
    }

    public static Double trans2Double(Object value){
        Double b;
        if (value instanceof Integer){
            b = Double.valueOf(((Integer) value).intValue());
        }else if (value == null || value.equals("")){
            b = new Double(0);
        }else{
            b = ((BigDecimal)value).doubleValue();
        }
        return b;
    }

    public static BigDecimal transInstance(Object value){
        BigDecimal b;
        if (value instanceof Integer){
            b = new BigDecimal((Integer)value);
        }else if (value == null || value.equals("")){
            b = new BigDecimal(0);
        }else{
            b = (BigDecimal)value;
        }
        return b;
    }

    public static Integer trans2Int(Object value){
        Integer b;
        if (value == null || value.equals("")){
            b = new Integer(0);
        }else{
            b = (Integer) value;
        }
        return b;
    }

    // 读取数据 不适用于聚合函数
    public static JSONArray readResultData(List<JSONObject> resultList, Relation relation) {
        JSONArray resultDatas = new JSONArray();
        if (relation.getGroup() != null){
            resultDatas = JSONArray.parseArray(JSON.toJSONString(resultList));
            return resultDatas;
        }
        for(JSONObject result : resultList){
            JSONObject json = new JSONObject();
            Map<String, Map<String, Object>> resultMap = CommonUtil.setResultMap(result, relation);
            for(String theme : resultMap.keySet()){
                Map<String, Object> ksMap = resultMap.get(theme);
                for(String key : ksMap.keySet()){
                    json.put(key, ksMap.get(key));
                }
            }
            resultDatas.add(json);
        }
        return resultDatas;
    }

    // 聚合函数读取map定制
    private static Map<String, Map<String, Object>> setAggrResultMap(JSONObject result, Relation relation){
        Map<String, Map<String, Object>> resultMap = new HashMap<>();
        Map<String, Object> temp = new HashMap<>();
        List<Aggregate> aggregateList = relation.getAggregateList();
        for(Aggregate aggregate : aggregateList) {
            String option = aggregate.getOption();
            List<ThemeRelationConfig> configList = aggregate.getThemeRelation();
            for (ThemeRelationConfig themeRelationConfig : configList) {
                String theme = themeRelationConfig.getTheme();
                List<String> keys = themeRelationConfig.getKeys();
                for (String key : keys) {
                    String mapkey = theme + "_" + key + option;
                    if (result.containsKey(mapkey)) {
                        temp.put(mapkey, result.get(mapkey));
                    }
                }
            }
        }
        String id = (String)result.get("_id");
        // 添加count的获取
        if (relation.getGroup() != null){
            String count = relation.getGroup().getCount();
            temp.put(count, result.get(count));
        }
        resultMap.put(id, temp);
        return resultMap;
    }

    // 聚合函数结果进行求平均
    public static Map<String, BigDecimal> getAvgFromResultMap(Map<String, Map<String, Object>> resultMap, List<String> indexNumList){
        Map<String, BigDecimal> jsonMap = new LinkedHashMap<>();
        List<String> shiftOrder = CommonConstant.SHIFT_ORDER;
        for(String shift : shiftOrder){
            BigDecimal sum = new BigDecimal(0);
            for(String indexNum : indexNumList) {
                if(resultMap.containsKey(shift) && resultMap.get(shift).containsKey(indexNum)){
                    if (resultMap.get(shift).get(indexNum) instanceof Integer){
                        sum = sum.add(new BigDecimal((Integer) resultMap.get(shift).get(indexNum)));
                    }else if(resultMap.get(shift).get(indexNum) instanceof String || resultMap.get(shift).get(indexNum) == null){// 如果取不到数 为""
                        sum = sum.add(new BigDecimal(0));
                    }else{
                        sum = sum.add((BigDecimal)resultMap.get(shift).get(indexNum));
                    }
                }
            }
            sum.divide(new BigDecimal((Integer) resultMap.get(shift).get("num")), 4);
            jsonMap.put(shift, sum);
        }
        return jsonMap;
    }

    // 聚合不分组求和
    public static Object getSumFromResultMap1(Map<String, Object> resultMap, List<String> indexNum){
        BigDecimal sum = new BigDecimal(0);
        for(String index: indexNum){
            if(resultMap.containsKey(index)){
                if (resultMap.get(index) instanceof Integer){
                    sum = sum.add(new BigDecimal((Integer) resultMap.get(index)));
                }else if(resultMap.get(index) instanceof String || resultMap.get(index) == null){// 如果取不到数 为""
                    sum = sum.add(new BigDecimal(0));
                }else{
                    sum = sum.add((BigDecimal)resultMap.get(index));
                }
            }
        }
        return sum;
    }

    public static BigDecimal getSumEachClass(Map<String, Object> resultEachClass, List<String> indexNumList) {
        BigDecimal sum = new BigDecimal(0);
        for(String indexNum : indexNumList) {
            if (resultEachClass.get(indexNum) instanceof Integer){
                sum = sum.add(new BigDecimal((Integer) resultEachClass.get(indexNum)));
            }else if(resultEachClass.get(indexNum) instanceof String){// 如果取不到数 为""
                sum = sum.add(new BigDecimal(0));
            }else{
                sum = sum.add((BigDecimal)resultEachClass.get(indexNum));
            }
        }
        return sum;
    }

  // 聚合函数结果进行分组求和
    public static Map<String, BigDecimal> getSumFromResultMap(Map<String, Map<String, Object>> resultMap, List<String> indexNumList){
        Map<String, BigDecimal> jsonMap = new LinkedHashMap<>();
        List<String> shiftOrder = CommonConstant.SHIFT_ORDER;
        for(String shift : shiftOrder){
            BigDecimal sum = new BigDecimal(0);
            for(String indexNum : indexNumList) {
                if(resultMap.containsKey(shift) && resultMap.get(shift).containsKey(indexNum)){
                    if (resultMap.get(shift).get(indexNum) instanceof Integer){
                        sum = sum.add(new BigDecimal((Integer) resultMap.get(shift).get(indexNum)));
                    }else if(resultMap.get(shift).get(indexNum) instanceof String){// 如果取不到数 为""
                        sum = sum.add(new BigDecimal(0));
                    }else{
                        sum = sum.add((BigDecimal)resultMap.get(shift).get(indexNum));
                    }
                }
            }
            jsonMap.put(shift, sum);
        }
        return jsonMap;
    }

    // 根据map进行排序
    public static JSONArray orderResult(Map<String, BigDecimal> jsonMap, String indexName){
        // map根据value排序
        List<Map.Entry<String,BigDecimal>> list = new ArrayList<Map.Entry<String,BigDecimal>>(jsonMap.entrySet());
        Collections.sort(list,new Comparator<Map.Entry<String, BigDecimal>>(){
            //降序排序
            public int compare(Map.Entry<String, BigDecimal> o1, Map.Entry<String, BigDecimal> o2) {
                return o2.getValue().compareTo(o1.getValue());
            }
        });
        JSONArray jsonArray = new JSONArray();
        int order = 1;
        for(Map.Entry<String, BigDecimal> mapping: list){
            JSONObject json = new JSONObject();
            json.put("orderBy", order ++);
            json.put("info_shift", mapping.getKey());
            json.put(indexName, mapping.getValue());
            jsonArray.add(json);
        }
        return jsonArray;
    }

    public static List<Object> getKvsFromResults(List<JSONObject> resultList, String theme, String index){
        List<Object> indexList = new ArrayList<>();
        for(JSONObject json: resultList){
            JSONArray themeData = (JSONArray) json.get(theme);
            if (!themeData.isEmpty()){
                JSONObject indexData = ((JSONObject)((JSONObject)((JSONObject)themeData.get(0)).get("kvs")).get("kv"));
                if (indexData.containsKey(index) && indexData.get(index) != null){
                    indexList.add(indexData.get(index));
                }
            }
        }
        return indexList;
    }

    // 根据result读取map
    public static Map<String, Map<String, Object>> setResultMap(JSONObject result, Relation relation){
        if (relation.getAggregateList() != null){
            return setAggrResultMap(result, relation);
        }
        Map<String, Map<String, Object>> resultMap = new HashMap<>();
        for(ThemeRelationConfig config : relation.getThemeRelation()){
            String theme = config.getTheme();
            Map<String, Object> kvMap = new HashMap<>();
            if (config.getKeys() != null){
                for(String key : config.getKeys()){
                    kvMap.put(key, CommonUtil.getValueFromJson(result, theme, key));
                }
            }

            if (config.getPass() != null && config.getPass().getValues()!=null ){
                String name = config.getPass().getName();
                for(String value : config.getPass().getValues()){
                    kvMap.put(value, CommonUtil.getPassValueFromJson(result, theme, name, value));
                }
            }
            if (config.getPass1() != null && config.getPass1().getValues()!=null ){
                String name = config.getPass1().getName();
                for(String value : config.getPass1().getValues()){
                    kvMap.put(value, CommonUtil.getPassValueFromJson(result, theme, name, value));
                }
            }
            if (config.getPass2() != null && config.getPass2().getValues()!=null ){
                String name = config.getPass2().getName();
                for(String value : config.getPass2().getValues()){
                    kvMap.put(value, CommonUtil.getPassValueFromJson(result, theme, name, value));
                }
            }
            resultMap.put(theme, kvMap);
        }
        return resultMap;
    }

    // 根据result读取该点数据
    public static Object getValueFromJson(JSONObject result, String theme, String key){
        if(result.containsKey(theme)){
            JSONArray themeData = result.getJSONArray(theme);
            if(themeData.isEmpty()){
                return null;
            }
            JSONObject json = (JSONObject) themeData.get(0);
            JSONObject kvList = json.getJSONObject("kvs").getJSONObject("kv");
            if(kvList.containsKey(key)){
                return kvList.get(key);
            }
        }
        return null;
    }

    // 根据result读取pass子集数据
    public static Object getPassValueFromJson(JSONObject result, String theme, String name, String key){
        if(result.containsKey(theme)){
            JSONArray themeData = result.getJSONArray(theme);
            if(themeData.isEmpty()){
                return null;
            }
            JSONObject json = (JSONObject) themeData.get(0);
            JSONObject kvList = json.getJSONObject("kvs").getJSONObject(name);
            if(kvList.containsKey(key)){
                return kvList.get(key);
            }
        }
        return null;
    }

    // 组装page
    public static JSONObject setPage(Integer pageNo, Integer pageSize){
        JSONObject page = new JSONObject();
        page.put("skip", (pageNo-1)*pageSize);
        page.put("limit", pageSize);
        return page;
    }

    // 组装sort
    public static JSONObject setSort(String name, Boolean asc, String theme){
        JSONObject sort = new JSONObject();
        sort.put("name", name);
        sort.put("asc", asc);
        sort.put("theme", theme);
        return sort;
    }


    public static BigDecimal getNotNullValue(BigDecimal... a) {
//        BigDecimal str = null;

        Optional<BigDecimal> any = Arrays.stream(a).filter(item -> item != null && item.compareTo(BigDecimal.ZERO) != 0).findAny();
        if (any.isPresent()){
            return any.get();
        }
//        for (BigDecimal s:a) {
//            if(s==null || BigDecimal.ZERO.compareTo(s)==0){
//              continue;
//            }
////            str.add(s);
//
//            return s;
//        }
        return null;
    }

//    /**
//     * 计算实际值中有值的数据
//     * @param a
//     * @return
//     */
//    public static int getPassRate(int m ,List<BigDecimal>list,BigDecimal... a) {
//        for (BigDecimal s:a) {
//            if(s==null || BigDecimal.ZERO.compareTo(s)==0){
//              continue;
//            }
//            if(compareTo(list.get(0),list.get(1),list.get(2))
//                    &&compareTo(list.get(3),list.get(4),list.get(5))
//                    &&compareTo(list.get(6),list.get(7),list.get(8))
//                    &&compareTo(list.get(9),list.get(10),list.get(11))
//                    &&compareTo(list.get(12),list.get(13),list.get(14))){
//                m++;
//            }
//
//        }
//        return m;
//    }

    /**
     * 判断 a 是否在max和min
     * @param a
     * @param max
     * @param min
     * @return
     */
    public static Boolean compareTo2(BigDecimal a,BigDecimal max,BigDecimal min) {
        if(a==null || a.compareTo(BigDecimal.ZERO)==0 ){
            return null;
        }
        if(max ==null || min == null){
            return false;
        }
        if(max.compareTo(BigDecimal.ZERO)==0 && min.compareTo(BigDecimal.ZERO)==0){
            return false;
        }
        int i = a.compareTo(max);
        //a大于等于max
        if(i == 1){
            return false;
        }
        int j = a.compareTo(min);
        //a小于等于min
        if(j == -1){
            return false;
        }

        return true;
    }
//    public static BigDecimal getMaxValue(BigDecimal... a) {
//        BigDecimal str = new BigDecimal("0.0");
//
//        for (BigDecimal s:a) {
//            if(s==null){
//                continue;
//            }
//            str.add(s);
//            if(str.compareTo(s)!=-1){
//                str = s;
//            }
//        }
//        return str;
//    }

    /**
     * 数值a是否在数值b的上下c%的范围之内
     * @param a
     * @param b
     * @param c
     * @return
     */
    public static Boolean calculateRange(BigDecimal a,BigDecimal b,BigDecimal c) {
        if(a==null || b == null){
            return false;
        }

        BigDecimal b_up = b.multiply(c.divide(BigDecimal.valueOf(100)).add(BigDecimal.ONE));
        BigDecimal b_down = b.multiply(BigDecimal.ONE.subtract(c.divide(BigDecimal.valueOf(100))));
        if(b_up.compareTo(BigDecimal.ZERO)==0 && b_down.compareTo(BigDecimal.ZERO)==0){
            return false;
        }
        int i = a.compareTo(b_up);
        /* a大于b_up */
        if(i == 1){
            return false;
        }
        int j = a.compareTo(b_down);
        /* a小于b_down */
        if(j == -1){
            return false;
        }
        return true;
    }
    /**
     * 判断 a 是否在max和min
     * @param a
     * @param max
     * @param min
     * @return
     */
    public static Boolean compareTo(BigDecimal a,BigDecimal max,BigDecimal min) {
        if(a==null || a.compareTo(BigDecimal.ZERO)==0 || max ==null || min == null){
            return false;
        }
        //如果max和min都为0，返回false
        if(max.compareTo(BigDecimal.ZERO)==0&&min.compareTo(BigDecimal.ZERO)==0){
            return false;
        }
        //如果max不为-1，那么需要判断a是否小于max---(-1可认为是正无穷)
        if(max.compareTo(BigDecimal.valueOf(-1))!=0){

            int i = a.compareTo(max);
            //a大于max
            if(i == 1){
                return false;
            }
        }
        //如果min不为-1，那么需要判断a是否大于min---(-1可认为是负无穷)
        if(min.compareTo(BigDecimal.valueOf(-1))!=0){

            int j = a.compareTo(min);
            //a小于min
            if(j == -1){
                return false;
            }
        }

        return true;
    }

    /**
     * BigDecimal 相乘
     */
    public static BigDecimal multiplication(Object object,Object o) {
        BigDecimal num = getNumber(object).multiply(getNumber(o));
        return num;
    }

    /**
     * 判断object属于什么基本类型的数据
     * @param object
     * @return
     */
    public static BigDecimal getNumber(Object object) {

        if(object == null){
            return null;
        }
        BigDecimal bd = new BigDecimal("0.0");

        if(object instanceof Integer){
            Integer i = (Integer)object;
            bd = new BigDecimal(i);
        }
        else if(object instanceof Double){
            Double i = (Double)object;
//            bd = new BigDecimal(i);
            bd = BigDecimal.valueOf(i);
        }
        else if(object instanceof BigDecimal){
            BigDecimal i = (BigDecimal)object;
            bd = i;
        }
        else if(object instanceof Long){
            Long i = (Long)object;
            bd = new BigDecimal(i);
        }
        else if(object instanceof Float){
            Float i = (Float)object;
            bd = new BigDecimal(i);
        }
        else if(object instanceof String){
            String i = (String)object;
            if("".equals(i)){
                return null;
            }
            bd = new BigDecimal(i);
        }
        return bd;
    }

    /**
     * 判断object属于什么基本类型的数据
     * @param object
     * @return 返回一个大于等于0的数
     */
    public static BigDecimal getFigure(Object object) {

        if(object == null){
            return BigDecimal.ZERO;
        }
        BigDecimal bd = BigDecimal.ZERO;

        if(object instanceof Integer){
            Integer i = (Integer)object;
            bd = new BigDecimal(i);
        }
        else if(object instanceof Double){
            Double i = (Double)object;
//            bd = new BigDecimal(i);
            bd = BigDecimal.valueOf(i);
        }
        else if(object instanceof BigDecimal){
            BigDecimal i = (BigDecimal)object;
            bd = i;
        }
        else if(object instanceof Long){
            Long i = (Long)object;
            bd = new BigDecimal(i);
        }
        else if(object instanceof Float){
            Float i = (Float)object;
            bd = new BigDecimal(i);
        }
        else if(object instanceof String){
            String i = (String)object;
            if("".equals(i)){
                return BigDecimal.ZERO;
            }
            bd = new BigDecimal(i);
        }
//        BigDecimal x = bd.divide(BigDecimal.ONE, RoundingMode.HALF_UP);
        if(bd.compareTo(BigDecimal.valueOf(0.00001)) >= 0){
            return bd;
        }else {
            return BigDecimal.ZERO;
        }

    }

    //标准差σ=sqrt(s^2)
    public static BigDecimal StandardDiviation(List<BigDecimal> x) {
        if(x == null || x.size()==0){
          return null;
        }
        int m=0;
//        double sum=0;
//        for(int i=0;i<m && x.get(i)!=null;i++){//求和
//            sum+=x.get(i).doubleValue();
//        }
        BigDecimal avg = AVG(x);//求平均值
        double dAve=avg.doubleValue();
        double dVar=0;
        for(int i=0;i<x.size();i++){//求方差
            if(x.get(i)==null){
                continue;
            }
            m++;
            dVar+=(x.get(i).doubleValue()-dAve)*(x.get(i).doubleValue()-dAve);
        }
        double re =Math.sqrt(dVar/m);
        return BigDecimal.valueOf(re);
    }
//
//    //方差
//    public static BigDecimal Variance(List<BigDecimal> x) {
//        int m=x.size();
//        double sum=0;
//        for(int i=0;i<m;i++){//求和
//            sum+=x.get(i).doubleValue();
//        }
//        double dAve=sum/m;//求平均值
//        double dVar=0;
//        for(int i=0;i<m;i++){//求方差
//            dVar+=(x.get(i).doubleValue()-dAve)*(x.get(i).doubleValue()-dAve);
//        }
//        return BigDecimal.valueOf(dVar);
//    }
//    //标准差σ=sqrt(s^2)
//    public static BigDecimal StandardDiviationByBigDecimal(List<BigDecimal> x) {
//        int m=x.size();
//        BigDecimal sum= new BigDecimal("0.0");
//        for(int i=0;i<m;i++){//求和
//
//            sum=sum.add(x.get(i));
//        }
//        BigDecimal dAve=sum.divide(BigDecimal.valueOf(m),2,RoundingMode.HALF_UP);
//        BigDecimal dVar=new BigDecimal("0.0");
//
//        for(int i=0;i<m;i++){//求方差
////            dVar+=(x.get(i)-dAve)*(x.get(i)-dAve);
//            BigDecimal n = x.get(i).subtract(dVar);
//            dVar=dVar.add(n.pow(2));
//        }
//
//        double a = Math.sqrt(dVar.doubleValue()/m);
//
//        return BigDecimal.valueOf(a);
//    }

    //平均值
    public static BigDecimal AVG(List<BigDecimal> x) {
        if(x==null || x.size()==0){
            return null;
        }
//        BigDecimal divide = x.stream().filter(s -> s != null).reduce(BigDecimal.ZERO, BigDecimal::add).divide(getNumber(x.size()),4,RoundingMode.HALF_UP);

        int m = 0;
        BigDecimal sum = new BigDecimal("0");
        for(int i = 0;i<x.size();i++){
           if(x.get(i) == null){
               continue;
           }
           m++;
           sum = sum.add(x.get(i));
        }
        BigDecimal divide = sum.divide(BigDecimal.valueOf(m), 4, RoundingMode.HALF_UP);
//        int m=x.size();
//        BigDecimal sum= new BigDecimal("0.0");
//        for(int i=0;i<m && x.get(i)!=null;i++){//求和
//
//            sum=sum.add(x.get(i));
//        }
//        BigDecimal dAve=sum.divide(BigDecimal.valueOf(m),4,RoundingMode.HALF_UP);
        return getSD(divide,4);
    }
    //平均值
    public static BigDecimal AVG(JSONArray x) {
        if(x==null || x.size()==0){
            return null;
        }
//        Object o = x.stream().filter(s -> s != null && BigDecimal.ZERO.compareTo(getNumber(s)) != 0).reduce((a, b) -> {
//            a = getNumber(a).add(getNumber(b));
//            return a;
//        }).get();
//        BigDecimal divide = getNumber(o).divide(getNumber(x.size()), 4, RoundingMode.HALF_UP);

        int m = 0;
        BigDecimal sum = new BigDecimal("0");
        for(int i = 0;i<x.size();i++){
            if(x.get(i) == null){
                continue;
            }
            m++;
            sum = sum.add(x.getBigDecimal(i));
        }
        BigDecimal divide = sum.divide(BigDecimal.valueOf(m), 4, RoundingMode.HALF_UP);

//        int m=x.size();
//        BigDecimal sum= new BigDecimal("0.0");
//        for(int i=0;i<m && x.get(i)!=null;i++){//求和
//
//            BigDecimal b = getNumber(x.get(i));
//            if(b != null){
//                sum=sum.add(b);
//            }
//        }
//        BigDecimal dAve=sum.divide(BigDecimal.valueOf(m),4,RoundingMode.HALF_UP);
        return divide;
    }
//    public static void main(String[] args) {
//        Double x[] = {53.0,61.0,49.0,67.0,63.0,55.0};
//        double bd = StandardDiviation(Arrays.asList(x));
        //6.191391873668904
//        System.out.println(bd);
//    }

//
//    //平均值
//    public static BigDecimal getMaxAndMinValue(List<BigDecimal> x) {
//        int m=x.size();
//        BigDecimal sum= new BigDecimal("0.0");
//        for(int i=0;i<m;i++){//求和
//
//            sum=sum.add(x.get(i));
//        }
//        BigDecimal dAve=sum.divide(BigDecimal.valueOf(m),2,RoundingMode.HALF_UP);
//        return dAve;
//    }

    public static BigDecimal getMaxValue(List<BigDecimal> x) {
        if(x == null || x.size() == 0){
            return null;
        }

        Optional<BigDecimal> max = x.stream().filter(s -> s != null).max((a, b) -> a.compareTo(b));
        if(max.isPresent()){
            BigDecimal bigDecimal = max.get();
            return bigDecimal;
        }

//        BigDecimal str = x.get(0);
//
//        for (BigDecimal s:x) {
//            if(s==null){
//                continue;
//            }
//            if(str == null){
//                str = s;
//            }else{
//                if(str.compareTo(s)==-1){
//                    str = s;
//                }
//            }
//        }
        return null;
    }

    public static BigDecimal getMinValue(List<BigDecimal> x) {
        if(x == null || x.size() == 0){
            return null;
        }
        Optional<BigDecimal> min = x.stream().filter(s -> s != null).min((a, b) -> a.compareTo(b));
        if(min.isPresent()){
            BigDecimal bigDecimal = min.get();
            return bigDecimal;
        }
//        BigDecimal str = x.get(0);
//
//        for (BigDecimal s:x) {
//            if(s==null){
//                continue;
//            }
//            if(str == null){
//                str = s;
//            }else{
//                if(str.compareTo(s)==1){
//                    str = s;
//                }
//            }
//        }
        return null;
    }


    public static String ReadFileContent(String fileName)  {

        File f = new File("C:/Users/Lenovo/Desktop/Content/"+fileName);
        String str = null;
        try {
            InputStream in = new FileInputStream(f);
            byte[] b = new byte[(int) f.length()];//数据存储的数组
            int len = 0;
            int temp = 0;
            while((temp = in.read()) != -1){//循环读取数据，未到达流的末尾
                b[len] = (byte) temp;//将有效数据存储在数组中
                len ++;
            }
            str = new String(b, 0, len, "GBK");
            in.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            if(str == null){
                str="";
            }
        }

        return str;
    }
    public static Map<Integer,Object> StatisticalSteelCoil(List<BigDecimal> x,int m){
        //计算最大值，最小值
        BigDecimal max = getMaxValue(x);
        BigDecimal min = getMinValue(x);

        //d= (max-min)/9
        BigDecimal d = max.subtract(min).divide(BigDecimal.valueOf(m),2, RoundingMode.HALF_UP);
        //统计区间中[min,min+d]----[min+8d,max]有多少钢卷
        //最小区间a,最大区间b
        BigDecimal a = new BigDecimal(0);
        BigDecimal b = new BigDecimal(0);
//        int i0=0;
//        int i1=0;
//        int i2=0;
//        int i3=0;
//        int i4=0;
//        int i5=0;
//        int i6=0;
//        int i7=0;
//        int i8=0;

        int[] ii =  new int[m];

        for(int i = 0;i<9;i++){

            a = min.add(d.multiply(BigDecimal.valueOf(i)));
            // i = 0 a=min b=min+d
            // i = 1 a=min+d b=min+2d
            // i = 2 a=min+2d b=min+3d
            //i = 8 a= min+8d b=max
            if(i==8){
                b = max;
            }else{
                b = min.add(d.multiply(BigDecimal.valueOf(i+1)));
            }
            for (int j = 0 ;j<x.size();j++){
                BigDecimal ys = x.get(j);
                if(compareTo(ys,b,a)){
                    ii[i]++;
//                    if(i==0){
//                        i0++;
//                    }else if(i==1){
//                        i1++;
//                    }else if(i==2){
//                        i2++;
//                    }else if(i==3){
//                        i3++;
//                    }else if(i==4){
//                        i4++;
//                    }else if(i==5){
//                        i5++;
//                    }else if(i==6){
//                        i6++;
//                    }else if(i==7){
//                        i7++;
//                    }else if(i==8){
//                        i8++;
//                    }
                }

            }
        }

        Map<Integer,Object>map = new HashMap<>();
        for(int i = 0;i<m;i++){
            map.put(i,ii[i]);
        }
//        map.put(0,i0);
//        map.put(1,i1);
//        map.put(2,i2);
//        map.put(3,i3);
//        map.put(4,i4);
//        map.put(5,i5);
//        map.put(6,i6);
//        map.put(7,i7);
//        map.put(8,i8);

        return map;
    }
    public static List<Object>  StatisticalSteelCoil(List<BigDecimal> x,int m,List<Object> coid_id_list){
        //计算最大值，最小值
        BigDecimal max = getSD(getMaxValue(x),4);
        BigDecimal min = getSD(getMinValue(x),4);

        //d= (max-min)/9
        BigDecimal d = max.subtract(min).divide(BigDecimal.valueOf(m),4, RoundingMode.HALF_UP);
        //统计区间中[min,min+d]----[min+8d,max]有多少钢卷
        //最小区间a,最大区间b
        BigDecimal a = new BigDecimal(0);
        BigDecimal b = new BigDecimal(0);

        int[] ii =  new int[m];

        Map<Integer,String>map=new HashMap<>();
        List<String>list_area = new ArrayList<>();
        for(int i = 0;i<m;i++){
            map.put(i,null);
        }
        for(int i = 0;i<m;i++){

            a = min.add(d.multiply(BigDecimal.valueOf(i)));

            if(i==8){
                b = max;
            }else{
                b = min.add(d.multiply(BigDecimal.valueOf(i+1)));
            }
            list_area.add(a+"-"+b);

            for (int j = 0 ;j<x.size();j++){
                BigDecimal ys = x.get(j);
                if(compareTo(ys,b,a)){
                    ii[i]++;
                    if(map.get(i)==null){
                        map.put(i, (String) coid_id_list.get(j));
                    }else{
                        String s = map.get(i);
                        map.put(i,s+";"+coid_id_list.get(j));
                    }
                }

            }
        }

//        Map<String,Object>map2 = new LinkedHashMap<>();
        List<Integer>list_num= new ArrayList<>();
        for(int i = 0;i<m;i++){
//            map2.put(list_area.get(i),ii[i]);
            list_num.add(ii[i]);
        }
        List<Object> list = new ArrayList<>();
        list.add(map);
//        list.add(map2);
//        list.add(map3);
        list.add(list_area);
        list.add(list_num);


        return list;
    }
    public static List<Object>  StatisticalCoil(List<BigDecimal> x,int m,List<Object> coid_id_list){
        //计算最大值，最小值
        BigDecimal max = getMaxValue(x).divide(BigDecimal.ONE,0,RoundingMode.HALF_UP);
        BigDecimal min = getMinValue(x).divide(BigDecimal.ONE,0,RoundingMode.HALF_UP);

        //d= (max-min)/9
        BigDecimal d = max.subtract(min).divide(BigDecimal.valueOf(m),0,RoundingMode.HALF_UP);
        //统计区间中[min,min+d]----[min+8d,max]有多少钢卷
        //最小区间a,最大区间b
        BigDecimal a = new BigDecimal(0);
        BigDecimal b = new BigDecimal(0);

        int[] ii =  new int[m];

        Map<Integer,String>map=new HashMap<>();
        List<String>list_area = new ArrayList<>();
        for(int i = 0;i<m;i++){
            map.put(i,null);
        }
        for(int i = 0;i<m;i++){

            a = min.add(d.multiply(BigDecimal.valueOf(i)));

            if(i==8){
                b = max;
            }else{
                b = min.add(d.multiply(BigDecimal.valueOf(i+1)));
            }
            list_area.add(a+"-"+b);

            for (int j = 0 ;j<x.size();j++){
                BigDecimal ys = x.get(j);
                if(compareTo(ys,b,a)){
                    ii[i]++;
                    if(map.get(i)==null){
                        map.put(i, (String) coid_id_list.get(j));
                    }else{
                        String s = map.get(i);
                        map.put(i,s+";"+coid_id_list.get(j));
                    }
                }

            }
        }

//        Map<String,Object>map2 = new LinkedHashMap<>();
        List<Integer>list_num= new ArrayList<>();
        for(int i = 0;i<m;i++){
//            map2.put(list_area.get(i),ii[i]);
            list_num.add(ii[i]);
        }
        List<Object> list = new ArrayList<>();
        list.add(map);
//        list.add(map2);
//        list.add(map3);
        list.add(list_area);
        list.add(list_num);


        return list;
    }

    /**
     * 数据库前15个与后15个按照1的间隔增加，中间的按照5间隔增加（中间数据个数随长度变化而变化）
     * 数据库l里层共有41个数据点（固定不变），没给点的间距按照带钢宽度（HSM_PDO//size_fm_ext_wid）/40计算。
     * @param y
     * @param list
     * @return
     */
//    public static Integer getArrayIndex(Double y, List<Double>list){
//        if(list==null || list.size()==0){
//            return null;
//        }
//        BigDecimal yy = BigDecimal.valueOf(y);
//        BigDecimal ii = BigDecimal.valueOf(0);
////        yy = yy.divide(BigDecimal.ONE,0,RoundingMode.HALF_UP);
//        int flag = (list.size()-30)*5+15;
//        BigDecimal flag_ii = BigDecimal.valueOf(flag).subtract(BigDecimal.valueOf(15)).divide(BigDecimal.valueOf(5)).add(BigDecimal.valueOf(14));
//        if(y<=14){
//            ii= yy.divide(BigDecimal.ONE,0,RoundingMode.HALF_UP);
//        }else if(y<25){
//            ii = BigDecimal.valueOf(15);
//        }
//        else if(y<=flag){
//            ii = yy.subtract(BigDecimal.valueOf(15)).divide(BigDecimal.valueOf(5)).add(BigDecimal.valueOf(14));
//        }else{
//            ii = yy.subtract(BigDecimal.valueOf(flag)).divide(BigDecimal.ONE,0,RoundingMode.HALF_UP).add(flag_ii);
//        }
//        return ii.intValue();
//    }

    /**
     * 保留b位有效数字
     * @param a
     * @param b
     * @return
     */
    public static BigDecimal getSD(BigDecimal a,Integer b){
        if(a == null)
            return null;
        return a.divide(BigDecimal.ONE,b,RoundingMode.HALF_UP);
    }

//    public static double NormalDistribution(float u,float v){
//
//        //生成两个(0~1)的随机数 x1,x2;
//        double x1=Math.random();
//        double x2=Math.random();
//
//        double Z0=Math.sqrt(-2*Math.log(x1))*Math.cos(2*Math.PI*x2);
//
//        return v*Z0+u;
//    }
//
//

//    /**
//     * 获取集合中数值与a最接近的数
//     * @param a
//     * @param b
//     * @return
//     */
//    public static BigDecimal getArrayMaxValue(BigDecimal a,List<BigDecimal>b){
//
//        if (b==null || b.size()==0){
//            return null;
//        }
//        BigDecimal m = b.get(0).subtract(a).abs();
//        int c = 0;
//        for (int i=0; i<b.size(); i++){
//
//            BigDecimal n = b.get(i).subtract(a).abs();
//            if (m.compareTo(n)>0){
//                m= n;
//                c = i;
//            }
//        }
//        return b.get(c);
//
//    }


        /**
         * @param sheetName  工作表名，文件名，头部信息
         * @param listName   列名
         * @param list       需要写入的数据
         */
        public static void excelPort(String sheetName, List<String> listName, List<Map<String, String>> list,HttpServletResponse response) {
            try {
                if (list.size() == 0) {
                    throw new BackingStoreException("数据为空");
                }
                // 声明一个工作簿
                HSSFWorkbook wb = new HSSFWorkbook();
                // 创建sheet页
                HSSFSheet sheet = wb.createSheet(sheetName);
                sheet.setDefaultColumnWidth(19);

                // 表头
                HSSFRow rowReportTitle = sheet.createRow(0);
                Cell cell1 = rowReportTitle.createCell(0); // 0列
                // 设置值
                cell1.setCellValue(sheetName);

                // 合并表头
                sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, listName.size() - 1));
                rowReportTitle.setHeight((short) 600); // 行高

                //设置表头字体
                Font headFont = wb.createFont();
                headFont.setFontName("宋体");
                headFont.setFontHeightInPoints((short) 18);// 字体大小

                CellStyle headStyle = wb.createCellStyle();
                headStyle.setFont(headFont);
                headStyle.setAlignment(CellStyle.ALIGN_CENTER);
                headStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
                // 头部样式添加
                cell1.setCellStyle(headStyle);


                // 记录标题信息
                TreeMap<String, Integer> headMap = new TreeMap<>();

                // 标题写入
                HSSFRow row = sheet.createRow(1);
                for (int i = 0; i < listName.size(); i++) {
                    row.setHeight((short) 450);
                    HSSFCell cell = row.createCell(i);
                    String headName = listName.get(i);
                    cell.setCellValue(headName); // 写入列名
                    headMap.put(headName, i);
                }

                // 写入内容数据
                int ind = 2;
                for (Map<String, String> map : list) {
                    HSSFRow r = sheet.createRow(ind++);
                    for (Map.Entry<String, Integer> m : headMap.entrySet()) {
                        String name = m.getKey(); // 列名
                        String value = map.get(name); // value 不一定存在
                        HSSFCell cell2 = r.createCell(m.getValue());
                        if (value != null) {
                            cell2.setCellValue(value);
                        }
                    }
                }

                // 输出Excel文件
                OutputStream output = response.getOutputStream();
                response.reset();
                // 设置文件头
                response.setHeader("Content-Disposition",
                        "attchement;filename=" + new String((sheetName + ".xls").getBytes("gb2312"), "ISO8859-1"));
                response.setContentType("application/msexcel");
                wb.write(output);
                wb.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        public static void excelPort(String sheetName, JSONArray list,HttpServletResponse response) {

            try {
                if (list.size() == 0) {
                    throw new BackingStoreException("数据为空");
                }
                // 声明一个工作簿
                HSSFWorkbook wb = new HSSFWorkbook();
                // 创建sheet页
                HSSFSheet sheet = wb.createSheet(sheetName);
                sheet.setDefaultColumnWidth(19);

                // 表头
                HSSFRow rowReportTitle = sheet.createRow(0);
                Cell cell1 = rowReportTitle.createCell(0); // 0列
                // 设置值
                cell1.setCellValue(sheetName);

                //设置表头字体
                Font headFont = wb.createFont();
                headFont.setFontName("宋体");
                headFont.setFontHeightInPoints((short) 18);// 字体大小

                CellStyle headStyle = wb.createCellStyle();
                headStyle.setFont(headFont);
                headStyle.setAlignment(CellStyle.ALIGN_CENTER);
                headStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
                // 头部样式添加
                cell1.setCellStyle(headStyle);

                // 记录标题信息
                TreeMap<String, Integer> headMap = new TreeMap<>();

                // 标题写入
                HSSFRow row = sheet.createRow(1);

                JSONObject jsonObject = list.getJSONObject(0);

                // 合并表头
                sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, jsonObject.keySet().size()- 1));
                rowReportTitle.setHeight((short) 600); // 行高

                int i = 0;
                for(String s: jsonObject.keySet()){

                    row.setHeight((short) 450);
                    HSSFCell cell = row.createCell(i);
                    String headName = s;
                    cell.setCellValue(headName); // 写入列名
                    headMap.put(headName, i);
                    i++;
                }

                // 写入内容数据
                int ind = 2;
                for (int m =0;m<list.size();m++){
                    HSSFRow r = sheet.createRow(ind++);
                    JSONObject data = list.getJSONObject(m);
                    for (Map.Entry<String, Integer> head : headMap.entrySet()) {
                        String name = head.getKey(); // 列名
                        String value = data.getString(name); // value 不一定存在
                        HSSFCell cell2 = r.createCell(head.getValue());
                        if (value != null) {
                            cell2.setCellValue(value);
                        }
                    }
                }

                // 输出Excel文件
                OutputStream output = response.getOutputStream();
                response.reset();
                // 设置文件头
                response.setHeader("Content-Disposition",
                        "attchement;filename=" + new String((sheetName + ".xls").getBytes("gb2312"), "ISO8859-1"));
                response.setContentType("application/msexcel");
                wb.write(output);
                wb.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

    public static void excelPort(String sheetName, JSONArray list,Map<String,String>map,HttpServletResponse response) {

        try {
            if (list.size() == 0) {
                throw new BackingStoreException("数据为空");
            }
            // 声明一个工作簿
            HSSFWorkbook wb = new HSSFWorkbook();
            // 创建sheet页
            HSSFSheet sheet = wb.createSheet(sheetName);
            sheet.setDefaultColumnWidth(19);

            // 表头
            HSSFRow rowReportTitle = sheet.createRow(0);
            Cell cell1 = rowReportTitle.createCell(0); // 0列
            // 设置值
            cell1.setCellValue(sheetName);

            //设置表头字体
            Font headFont = wb.createFont();
            headFont.setFontName("宋体");
            headFont.setFontHeightInPoints((short) 18);// 字体大小

            CellStyle headStyle = wb.createCellStyle();
            headStyle.setFont(headFont);
            headStyle.setAlignment(CellStyle.ALIGN_CENTER);
            headStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
            // 头部样式添加
            cell1.setCellStyle(headStyle);

            // 记录标题信息
            TreeMap<String, Integer> headMap = new TreeMap<>();

            // 标题写入
            HSSFRow row = sheet.createRow(1);

            JSONObject jsonObject = list.getJSONObject(0);

            // 合并表头
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, jsonObject.keySet().size()- 1));
            rowReportTitle.setHeight((short) 600); // 行高

            int i = 0;
            for(String s: map.keySet()){

                row.setHeight((short) 450);
                HSSFCell cell = row.createCell(i);
                String headName = s;
                String headNameZn = map.get(headName);
                cell.setCellValue(headNameZn); // 写入列名
                headMap.put(headName, i);
                i++;
            }


            // 写入内容数据
            int ind = 2;
            for (int m =0;m<list.size();m++){
                HSSFRow r = sheet.createRow(ind++);
                JSONObject data = list.getJSONObject(m);
                for (Map.Entry<String, Integer> head : headMap.entrySet()) {
                    String name = head.getKey(); // 列名
                    String value = data.getString(name); // value 不一定存在
                    HSSFCell cell2 = r.createCell(head.getValue());
                    if (value != null) {
                        cell2.setCellValue(value);
                    }
                }
            }

            // 输出Excel文件
            OutputStream output = response.getOutputStream();
            response.reset();
            // 设置文件头
            response.setHeader("Content-Disposition",
                    "attchement;filename=" + new String((sheetName + ".xls").getBytes("gb2312"), "ISO8859-1"));
            response.setContentType("application/msexcel");
            wb.write(output);
            wb.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     *
     * @param time_start
     * @param time_end
     * @return
     */
    public static String compareTime(String time_start,String time_end) {
        DateTimeFormatter df = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
            LocalDateTime localDateStart = LocalDateTime.parse(time_start,df);
            LocalDateTime localDateEnd = LocalDateTime.parse(time_end,df);
            LocalDateTime week = localDateEnd.minusWeeks(1);
            if(week.compareTo(localDateStart)>0){
                    return week.format(df);
                }else{
                    return localDateStart.format(df);
                }
        }

    /**
     *
     * @param content
     * @return
     */
//    public static String addQueryRecord(String name,JSONObject content, QueryRecordDao queryRecordDao) {
//        QueryRecord queryRecord = new QueryRecord();
//        queryRecord.setRequetInterface(name);
//        queryRecord.setSelectName(content.getJSONObject("body").get("operator")!=null?content.getJSONObject("body").get("operator")+"":"other");
//        queryRecord.setSelectTime(new Date());
//        queryRecord.setSelectParams(content.toJSONString());
//        queryRecordDao.save(queryRecord);
//        return "ok";
//
//    }
    public static String getNotNullString(Object... os) {
        for(Object o : os){
            if (o != null){
                if(o instanceof String){
                    return (String)o;
                }
            }
        }
        return null;
    }
    public static void main(String[] args) {
//        String studentString = "{\"id\":1,\"age\":2,\"name\":\"zhang\"}";
//        String studentString = "{\"code\":200,\"msg\":\"success\",\"slabTemp\":[498.2047424316406,603.7476806640625,1080.209716796875,1226.234619140625]}";
//        String studentString = "{\"code\":200,\"msg\":\"success\",\"slabTemp\":[498.2047424316406,603.7476806640625,1080.209716796875,1226.234619140625]}";
        String studentString = "{\"code\":200,\"msg\":\"success\",\"slabTemp\":[502.3059997558594,604.2699584960938,1078.5760498046875,1228.7724609375]}";

//        studentString = JSON.toJSONString(studentString);
        JSONObject jsonObject1 = JSON.parseObject(studentString);

        System.out.println(jsonObject1);

        //        DateTimeFormatter df = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
//        String time_start = "2022-10-05 17:16:44";
//        String time_end = "2022-11-20 17:16:44";
//
//        System.out.println(compareTime(time_start,time_end));
//
//        DateTimeFormatter newFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
//        LocalDateTime date2 = LocalDateTime.now();
//        System.out.println(date2.format(newFormatter));
        //        DateTimeFormatter df = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
//        String time_end = "2022-11-09 17:16:44";
//        LocalDate localDateStart = LocalDate.parse(time_start,df);
//
//        LocalDate week = LocalDate.now().minusWeeks(1);
//        if(week.compareTo(localDateStart)>0){
//
//            System.out.println("week"+week);
//        }else{
//            System.out.println("localDateStart"+localDateStart);
//
//        }



//        LocalDate localDateEnd = LocalDate.parse(time_end,df);
//        ChronoUnit.WEEKS.between(localDateStart,localDateEnd);

//        Calendar calendar = Calendar.getInstance();  //得到日历
//        calendar.setTime(new Date());//把当前时间赋给日历
//        calendar.add(Calendar.DAY_OF_MONTH, -7);  //设置为7天前
//        Date before7days = calendar.getTime();   //得到7天前的时间
//        Date before7day1 = new Date();
//        System.out.println("----"+before7days.getTime());
//        System.out.println("----"+localDateStart.getLong());
//        if(before7days.getTime() < localDateStart.getTime()){
//            return true;
//        }else{
//            return false;
//        }
//        System.out.println(before7days);

//        List x = Arrays.asList(133,123,56,14,67);
//        Optional reduce = x.stream().reduce((acce, item) -> {
//            acce = getNumber(item).add(getNumber(acce));
//            return acce;
//        });
//        System.out.println(reduce.get());
//        List<BigDecimal> x = Arrays.asList(getNumber(120.6),getNumber(70.66),getNumber(110),null,getNumber(5),getNumber(17));
//        BigDecimal bigDecimal = x.stream().filter(s->s!=null).min((a, b) -> a.compareTo(b)).get();
//        DoubleSummaryStatistics doubleSummaryStatistics = x.stream().filter(s->s!=null).mapToDouble(d -> d.doubleValue()).summaryStatistics();
//        System.out.println(bigDecimal);
//        System.out.println(doubleSummaryStatistics.getMax());
//
//        BigDecimal divide = x.stream().filter(s -> s != null).reduce(BigDecimal.ZERO, BigDecimal::add).divide(getNumber(x.size()));
//
//        System.out.println(divide);

//        Optional<BigDecimal> any = x.stream().filter(item -> item != null && item.compareTo(BigDecimal.ZERO) != 0).findAny();
//        if (any.isPresent()){
//            System.out.println(any.get());
//
//        }else {
//            System.out.println("null");
//        }

//        JSONArray trk_center_speed_list = new JSONArray();
//        trk_center_speed_list.add(new BigDecimal(100));
//        trk_center_speed_list.add(new BigDecimal(110));
//        trk_center_speed_list.add(new BigDecimal(120));
//        trk_center_speed_list.add(new BigDecimal(130));
//        trk_center_speed_list.add(new BigDecimal(140));
//        trk_center_speed_list.add(new BigDecimal(150));
//        trk_center_speed_list.add(null);
//        trk_center_speed_list.add(new BigDecimal(0));

//        System.out.println( AVG(trk_center_speed_list));


//                /* 创建数组，用来构建不同大小的List */
//                Integer[] intArr = new Integer[20000];
//                List<Integer> intList = Arrays.asList(intArr);
//
//                List<Integer> forList = new ArrayList<>();
//                List<Integer> streamForList = new ArrayList<>();
//                List<Integer> parallelStreamForList = new CopyOnWriteArrayList<>();
//
//
//                intList.forEach(l -> forList.add(l));
//                intList.stream().forEach(l -> streamForList.add(l));
//                intList.parallelStream().forEach((l)->{
//                    parallelStreamForList.add(l);
//                });
//
//                System.out.println(forList.size() + "---普通for循环数组");
//                System.out.println(streamForList.size() + "---stream流for循环数组");
//                System.out.println(parallelStreamForList.size() + "---parallelStream流for循环数组");
//
//        Map<String,JSONObject> dataMap = new HashMap<>();
//        JSONArray J = new JSONArray();
//        List<Object>list = new CopyOnWriteArrayList<>();
//        for (int i = 0 ;i<100000;i++){
//            JSONObject jsonObject = new JSONObject();
//            jsonObject.put(i+"",i);
//            dataMap.put(i+"",jsonObject);
//        }
//        Lock lock = new ReentrantLock();
//        long startTime = System.currentTimeMillis();
//        dataMap.keySet().parallelStream().forEach((key)->{
//            JSONObject jsonObject = new JSONObject();
//            jsonObject.put(key,dataMap.get(key));
//            lock.lock();
//            J.add(jsonObject);
////            list.add(jsonObject);
//            lock.unlock();
//        });
//
//        long endTime = System.currentTimeMillis();
//        System.out.println("程序运行时间：" + (endTime - startTime) + "ms");
//        System.out.println(J.size());
//        J.stream().forEach(key->{
//            String s =  key+"";
//            System.out.println(s);
//        });



        /*要计算的程序部分*/

        //结束时间
        //打印

    }

    public static boolean isNotNull(String ipAddress) {

        if (ipAddress == null){
            return false;
        }
        else if ("".equals(ipAddress)){
            return false;
        }
        return true;
    }

    public static String getIpAddress(HttpServletRequest request) {
        // 首先, 获取 X-Forwarded-For 中的 IP 地址，它在 HTTP 扩展协议中能表示真实的客户端 IP
        String ipAddress = request.getHeader("X-Forwarded-For");
        if (isNotNull(ipAddress) && !"unknown".equalsIgnoreCase(ipAddress)) {
            // 多次反向代理后会有多个 ip 值，第一个 ip 才是真实 ip, 例: X-Forwarded-For: client, proxy1, proxy2，proxy…
            int index = ipAddress.indexOf(",");
            if (index != -1) {
                return ipAddress.substring(0, index);
            }

            return ipAddress;
        }

        // 如果 X-Forwarded-For 获取不到, 就去获取 X-Real-IP
        ipAddress = request.getHeader("X-Real-IP");

        if (!isNotNull(ipAddress) || "unknown".equalsIgnoreCase(ipAddress)) {
            // 如果 X-Real-IP 获取不到, 就去获取 Proxy-Client-IP
            ipAddress = request.getHeader("Proxy-Client-IP");
        }
        if (!isNotNull(ipAddress) || "unknown".equalsIgnoreCase(ipAddress)) {
            // 如果 Proxy-Client-IP 获取不到, 就去获取 WL-Proxy-Client-IP
            ipAddress = request.getHeader("WL-Proxy-Client-IP");
        }
        if (!isNotNull(ipAddress) || "unknown".equalsIgnoreCase(ipAddress)) {
            // 如果 WL-Proxy-Client-IP 获取不到, 就去获取 HTTP_CLIENT_IP
            ipAddress = request.getHeader("HTTP_CLIENT_IP");
        }
        if (!isNotNull(ipAddress) || "unknown".equalsIgnoreCase(ipAddress)) {
            // 如果 HTTP_CLIENT_IP 获取不到, 就去获取 HTTP_X_FORWARDED_FOR
            ipAddress = request.getHeader("HTTP_X_FORWARDED_FOR");
        }
        if (!isNotNull(ipAddress) || "unknown".equalsIgnoreCase(ipAddress)) {
            // 都获取不到, 最后才通过 request.getRemoteAddr() 获取IP
            ipAddress = request.getRemoteAddr();
        }

        return "0:0:0:0:0:0:0:1".equals(ipAddress) ? "127.0.0.1" : ipAddress;
    }

    public static boolean isIpAddressInSubnet(String ipAddress, String subnetAddress, String subnetMask) throws UnknownHostException {
        InetAddress inetAddress = InetAddress.getByName(ipAddress);
        InetAddress subnet = InetAddress.getByName(subnetAddress);
        InetAddress mask = InetAddress.getByName(subnetMask);

        byte[] inetAddressBytes = inetAddress.getAddress();
        byte[] subnetBytes = subnet.getAddress();
        byte[] maskBytes = mask.getAddress();

        for (int i = 0; i < inetAddressBytes.length; i++) {
            int addressByte = inetAddressBytes[i] & 0xFF;
            int subnetByte = subnetBytes[i] & 0xFF;
            int maskByte = maskBytes[i] & 0xFF;

            if ((addressByte & maskByte) != (subnetByte & maskByte)) {
                return false;
            }
        }

        return true;
    }

}
